package pub.cleangao.mybatis.dao;


import pub.cleangao.common.util.StringUtil;
import pub.cleangao.mybatis.expression.Expressions;
import pub.cleangao.mybatis.expression.SqlLiteral;
import pub.cleangao.mybatis.expression.Tables;

import java.util.*;

/**
 * 查询类
 *
 * <pre>
 * 查询姓名为张三，并且年龄为22岁的用户：
 * Query query = new Query().eq("username","张三").eq("age",22);
 * List<User> users = mapper.list(query);
 *
 * 查询年龄为10,20,30的用户：
 * Query query = new Query().in("age",Arrays.asList(10,20,30));
 * List<User> users = mapper.list(query);
 *
 * 查询注册日期大于2017-11-11的用户：
 * Date regDate = ...
 * Query query = new Query().gt("reg_date",regDate);
 * List<User> users = mapper.list(query);
 *
 * 查询性别为男的，年龄大于等于20岁的用户，按年龄降序：
 * Query query = new Query().eq("gender",1).ge("age",20).orderby("age",Sort.DESC);
 * List<User> users = mapper.list(query);
 *
 * 分页查询：
 * Query query = new Query().eq("age",10).page(1,10); // 第一页，每页10条数据
 * List<User> users = mapper.list(query);
 *
 * 查询总记录数：
 * Query query = new Query().eq("age",10).page(1,10); // 第一页，每页10条数据
 * long total = mapper.getCount(query); // 该条件下总记录数
 * </pre>
 */
public class Query {


    /**
     * 查询全部记录
     */
    private boolean queryAll = false;
    /**
     * true:使用逻辑删除
     */
    private boolean force = false;

    /**
     * 总数已经设置
     */
    private boolean totalSet = false;

    private long total = 0L;

    private long offset = 0L;

    private int pageSize = 35;


    /**
     * 条件表达式
     */
    private final Expressions expressions = new Expressions();

    /**
     * 从以下数据表查询
     */
    private final Tables tables = new Tables();

    // ------------ 基本条件 ------------./s

    /**
     * 添加等于条件
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query eq(String columnName, Object value) {
        expressions.eq(columnName, value);
        return this;
    }

    public Query orEq(String columnName, Object value) {
        expressions.orEq(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加等于条件
     * <p>
     * query.eq(StringUtils.hasText(name), "name", name);
     * 等同于：
     * if (StringUtils.hasText(name)) {
     * query.eq("name", name);
     * }
     * </p>
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query eq(boolean condition, String columnName, Object value) {
        if (condition) {
            eq(columnName, value);
        }
        return this;
    }

    public Query orEq(boolean condition, String columnName, Object value) {
        if (condition) {
            orEq(columnName, value);
        }
        return this;
    }

    /**
     * 添加不等于条件
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query notEq(String columnName, Object value) {
        expressions.notEq(columnName, value);
        return this;
    }

    public Query orNotEq(String columnName, Object value) {
        expressions.orNotEq(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加不等于条件
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query notEq(boolean condition, String columnName, Object value) {
        if (condition) {
            notEq(columnName, value);
        }
        return this;
    }

    public Query orNotEq(boolean condition, String columnName, Object value) {
        if (condition) {
            orNotEq(columnName, value);
        }
        return this;
    }

    /**
     * 添加大于条件,>
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query gt(String columnName, Object value) {
        expressions.gt(columnName, value);
        return this;
    }

    public Query orGt(String columnName, Object value) {
        expressions.orGt(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加大于条件,>
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query gt(boolean condition, String columnName, Object value) {
        if (condition) {
            gt(columnName, value);
        }
        return this;
    }

    public Query orGt(boolean condition, String columnName, Object value) {
        if (condition) {
            orGt(columnName, value);
        }
        return this;
    }

    /**
     * 添加大于等于条件,>=
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query ge(String columnName, Object value) {
        expressions.ge(columnName, value);
        return this;
    }

    public Query orGe(String columnName, Object value) {
        expressions.orGe(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加大于等于条件,>=
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query ge(boolean condition, String columnName, Object value) {
        if (condition) {
            ge(columnName, value);
        }
        return this;
    }

    public Query orGe(boolean condition, String columnName, Object value) {
        if (condition) {
            orGe(columnName, value);
        }
        return this;
    }

    /**
     * 添加小于条件,<
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query lt(String columnName, Object value) {
        expressions.lt(columnName, value);
        return this;
    }

    public Query orLt(String columnName, Object value) {
        expressions.orLt(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加小于条件,<
     *
     * @param expression 表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query lt(boolean expression, String columnName, Object value) {
        if (expression) {
            lt(columnName, value);
        }
        return this;
    }

    public Query orLt(boolean expression, String columnName, Object value) {
        if (expression) {
            orLt(columnName, value);
        }
        return this;
    }

    /**
     * 小于等于,<=
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query le(String columnName, Object value) {
        expressions.le(columnName, value);
        return this;
    }

    public Query orLe(String columnName, Object value) {
        expressions.orLe(columnName, value);
        return this;
    }

    /**
     * 根据表达式小于等于条件,<=
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query le(boolean condition, String columnName, Object value) {
        if (condition) {
            le(columnName, value);
        }
        return this;
    }

    public Query orLe(boolean condition, String columnName, Object value) {
        if (condition) {
            orLe(columnName, value);
        }
        return this;
    }

    /**
     * like 查询
     *
     * <p>%在value中设置</p>
     */
    public Query like(String columnName, String value) {
        expressions.like(columnName, value);
        return this;
    }

    public Query orLike(String columnName, String value) {
        expressions.orLike(columnName, value);
        return this;
    }

    public Query like(boolean condition, String columnName, String value) {
        if (condition) {
            like(columnName, value);
        }
        return this;
    }

    public Query orLike(boolean condition, String columnName, String value) {
        if (condition) {
            orLike(columnName, value);
        }
        return this;
    }


    /**
     * 添加IN条件
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query in(String columnName, Collection<?> value) {
        expressions.in(columnName, value);
        return this;
    }

    public Query orIn(String columnName, Collection<?> value) {
        expressions.orIn(columnName, value);
        return this;
    }

    public Query in(boolean condition, String columnName, Collection<?> value) {
        if (condition) {
            in(columnName, value);
        }
        return this;
    }

    public Query orIn(boolean condition, String columnName, Collection<?> value) {
        if (condition) {
            orIn(columnName, value);
        }
        return this;
    }


    /**
     * 添加IN条件
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query in(String columnName, Object[] value) {
        expressions.in(columnName, value);
        return this;
    }

    public Query orIn(String columnName, Object[] value) {
        expressions.orIn(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加IN条件
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query in(boolean condition, String columnName, Object[] value) {
        if (condition) {
            in(columnName, value);
        }
        return this;
    }

    public Query orIn(boolean condition, String columnName, Object[] value) {
        if (condition) {
            orIn(columnName, value);
        }
        return this;
    }

    /**
     * 添加not in条件
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query notIn(String columnName, Collection<?> value) {
        expressions.notIn(columnName, value);
        return this;
    }

    public Query orNotIn(String columnName, Collection<?> value) {
        expressions.orNotIn(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加not in条件
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query notIn(boolean condition, String columnName, Collection<?> value) {
        if (condition) {
            notIn(columnName, value);
        }
        return this;
    }

    public Query orNotIn(boolean condition, String columnName, Collection<?> value) {
        if (condition) {
            orNotIn(columnName, value);
        }
        return this;
    }


    /**
     * 添加not in条件
     *
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query notIn(String columnName, Object[] value) {
        expressions.notIn(columnName, value);
        return this;
    }

    public Query orNotIn(String columnName, Object[] value) {
        expressions.orNotIn(columnName, value);
        return this;
    }

    /**
     * 根据表达式添加not in条件
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param value      值
     * @return 返回Query对象
     */
    public Query notIn(boolean condition, String columnName, Object[] value) {
        if (condition) {
            notIn(columnName, value);
        }
        return this;
    }

    public Query orNotIn(boolean condition, String columnName, Object[] value) {
        if (condition) {
            orNotIn(columnName, value);
        }
        return this;
    }

    /**
     * 添加between条件
     *
     * @param columnName 数据库字段名
     * @param startValue 起始值
     * @param endValue   结束值
     * @return 返回Query对象
     */
    public Query between(String columnName, Object startValue, Object endValue) {
        expressions.between(columnName, startValue, endValue);
        return this;
    }

    public Query orBetween(String columnName, Object startValue, Object endValue) {
        expressions.orBetween(columnName, startValue, endValue);
        return this;
    }

    /**
     * 添加between条件
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param startValue 起始值
     * @param endValue   结束值
     * @return 返回Query对象
     */
    public Query between(boolean condition, String columnName, Object startValue, Object endValue) {
        if (condition) {
            between(columnName, startValue, endValue);
        }
        return this;
    }

    public Query orBetween(boolean condition, String columnName, Object startValue, Object endValue) {
        if (condition) {
            orBetween(columnName, startValue, endValue);
        }
        return this;
    }

    /**
     * 添加between条件
     * <p>
     * Object[] arr = new Object[]{1, 100};
     * query.between(arr);
     * </p>
     *
     * @param values 存放起始值、结束值，只能存放2个值，values[0]表示开始值，value[1]表示结束值
     * @return 返回Query对象
     */
    public Query between(String columnName, Object[] values) {
        return between(columnName, values[0], values[1]);
    }

    public Query orBetween(String columnName, Object[] values) {
        return orBetween(columnName, values[0], values[1]);
    }

    /**
     * 添加between条件
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param values     存放起始值、结束值，只能存放2个值，values[0]表示开始值，value[1]表示结束值
     * @return 返回Query对象
     */
    public Query between(boolean condition, String columnName, Object[] values) {
        if (condition) {
            between(columnName, values);
        }
        return this;
    }

    public Query orBetween(boolean condition, String columnName, Object[] values) {
        if (condition) {
            orBetween(columnName, values);
        }
        return this;
    }

    /**
     * 添加between条件
     * <p>
     * query.between(Arrays.asList(1, 100));
     * </p>
     *
     * @param values 存放起始值、结束值，只能存放2个值
     * @return 返回Query对象
     */
    public Query between(String columnName, List<?> values) {
        return between(columnName, values.get(0), values.get(1));
    }

    public Query orBetween(String columnName, List<?> values) {
        return orBetween(columnName, values.get(0), values.get(1));
    }

    /**
     * 添加between条件
     *
     * @param condition  表达式，当为true时添加条件
     * @param columnName 数据库字段名
     * @param values     存放起始值、结束值，只能存放2个值
     * @return 返回Query对象
     */
    public Query between(boolean condition, String columnName, List<?> values) {
        if (condition) {
            between(columnName, values);
        }
        return this;
    }

    public Query orBetween(boolean condition, String columnName, List<?> values) {
        if (condition) {
            orBetween(columnName, values);
        }
        return this;
    }

    /**
     * 添加between条件
     * <pre>
     * {@literal
     * query.between(new BetweenValue(1, 100));
     * }
     * </pre>
     *
     * @param betweenValue 起始值、结束值包装对象
     * @return 返回Query对象
     */
    public Query between(String columnName, BetweenValue betweenValue) {
        return between(columnName, betweenValue.getStartValue(), betweenValue.getEndValue());
    }

    public Query orBetween(String columnName, BetweenValue betweenValue) {
        return orBetween(columnName, betweenValue.getStartValue(), betweenValue.getEndValue());
    }

    /**
     * 添加between条件
     *
     * @param condition    表达式，当为true时添加条件
     * @param columnName   数据库字段名
     * @param betweenValue 存放起始值、结束值，只能存放2个值
     * @return 返回Query对象
     */
    public Query between(boolean condition, String columnName, BetweenValue betweenValue) {
        if (condition) {
            between(columnName, betweenValue);
        }
        return this;
    }

    public Query orBetween(boolean condition, String columnName, BetweenValue betweenValue) {
        if (condition) {
            orBetween(columnName, betweenValue);
        }
        return this;
    }


    public Query last(String sql) {
        expressions.lastSql(sql);
        return this;
    }

    /**
     * 根据表达式添加自定义sql条件
     *
     * @param condition 表达式，当为true时添加条件
     * @param sql       自定义sql
     * @return 返回Query对象
     */
    public Query last(boolean condition, String sql) {
        if (condition) {
            last(sql);
        }
        return this;
    }

    /**
     * 添加自定义sql条件
     *
     * @param sqlFormat SQL模板，参数值使用?代替，如：<code>username = ? and nickname like '%?%'</code>
     * @param args      参数
     * @return 返回Query对象
     */
    public Query last(String sqlFormat, Object... args) {
        if (args != null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                Object arg = args[i];
                if (arg instanceof CharSequence) {
                    args[i] = arg.toString().replaceAll("([';])+|(--)+", SqlLiteral.EMPTY);
                }
            }
            for (Object arg : args) {
                sqlFormat = sqlFormat.replaceFirst(String.format("\\%s", "?"), String.valueOf(arg));
            }
        }
        expressions.lastSql(sqlFormat);
        return this;
    }


    /**
     * 添加字段不为null的条件
     *
     * @param column 数据库字段名
     * @return 返回Query对象
     */
    public Query notNull(String column) {
        expressions.isNotNull(column);
        return this;
    }

    public Query orNotNull(String column) {
        expressions.orIsNotNull(column);
        return this;
    }

    /**
     * 根据表达式添加字段不为null的条件
     *
     * @param condition 表达式，当为true时添加条件
     * @param column    数据库字段名
     * @return 返回Query对象
     */
    public Query notNull(boolean condition, String column) {
        if (condition) {
            notNull(column);
        }
        return this;
    }

    public Query orNotNull(boolean condition, String column) {
        if (condition) {
            orNotNull(column);
        }
        return this;
    }

    /**
     * 添加字段是null的条件
     *
     * @param column 数据库字段名
     * @return 返回Query对象
     */
    public Query isNull(String column) {
        expressions.isNull(column);
        return this;
    }

    public Query orIsNull(String column) {
        expressions.orIsNull(column);
        return this;
    }

    /**
     * 根据表达式添加字段是null的条件
     *
     * @param condition 表达式，当为true时添加条件
     * @param column    数据库字段名
     * @return 返回Query对象
     */
    public Query isNull(boolean condition, String column) {
        if (condition) {
            isNull(column);
        }
        return this;
    }

    public Query orIsNull(boolean condition, String column) {
        if (condition) {
            orIsNull(column);
        }
        return this;
    }

    /**
     * 添加不为空字符串条件
     *
     * @param column 数据库字段名
     * @return 返回Query对象
     */
    public Query notEmpty(String column) {
        expressions.isNotEmpty(column);
        return this;
    }

    public Query orNotEmpty(String column) {
        expressions.orIsNotEmpty(column);
        return this;
    }

    /**
     * 根据表达式添加不为空字符串条件
     *
     * @param condition 表达式，当为true时添加条件
     * @param column    数据库字段名
     * @return 返回Query对象
     */
    public Query notEmpty(boolean condition, String column) {
        if (condition) {
            notEmpty(column);
        }
        return this;
    }

    public Query orNotEmpty(boolean condition, String column) {
        if (condition) {
            orNotEmpty(column);
        }
        return this;
    }

    /**
     * 添加空字段条件，null或者空字符串
     *
     * @param column 数据库字段名
     * @return 返回Query对象
     */
    public Query isEmpty(String column) {
        expressions.isEmpty(column);
        return this;
    }

    public Query orIsEmpty(String column) {
        expressions.orIsEmpty(column);
        return this;
    }

    /**
     * 根据表达式添加空字段条件，null或者空字符串
     *
     * @param condition 表达式，当为true时添加条件
     * @param column    数据库字段名
     * @return 返回Query对象
     */
    public Query isEmpty(boolean condition, String column) {
        if (condition) {
            isEmpty(column);
        }
        return this;
    }

    public Query orIsEmpty(boolean condition, String column) {
        if (condition) {
            orIsEmpty(column);
        }
        return this;
    }

    public Query eq(Map<String, Object> map) {
        Set<String> keys = map.keySet();
        for (String columnName : keys) {
            if (StringUtil.hasText(columnName)) {
                this.eq(columnName, map.get(columnName));
            }
        }
        return this;
    }

    public Query eq(boolean condition, Map<String, Object> map) {
        if (condition) {
            eq(map);
        }
        return this;
    }

    public Query force(boolean force) {
        this.force = force;
        return this;
    }

    public Query force() {
        this.force = true;
        return this;
    }


    /**
     * 排序
     *
     * @param columnName 列名
     */
    public Query orderByAsc(String columnName) {
        expressions.orderBy(columnName, Sort.ASC);
        return this;
    }

    public Query orderByDesc(String columnName) {
        expressions.orderBy(columnName, Sort.DESC);
        return this;
    }


    /**
     * 设置分页信息
     *
     * @param pageIndex 当前第几页,从1开始
     * @param pageSize  每页结果集大小
     * @return 返回Query对象
     */
    public Query page(int pageIndex, int pageSize) {
        if (pageIndex < 1) {
            throw new IllegalArgumentException("must pageIndex >= 1");
        }
        if (pageSize < 1) {
            throw new IllegalArgumentException("must pageSize >= 1");
        }
        long offset = ((pageIndex - 1) * (long) pageSize);
        expressions.limit(offset, pageSize);
        this.offset = offset;
        this.pageSize = pageSize;
        return this;
    }

    /**
     * 设置分页信息,针对不规则分页。对应mysql分页语句：limit {start},{offset}
     *
     * @param offset   记录起始位置
     * @param pageSize 查询多少条记录
     * @return 返回Query对象
     */
    public Query limit(long offset, int... pageSize) {
        expressions.limit(offset, pageSize);
        this.offset = offset;
        if (pageSize != null && pageSize.length > 0) {
            this.pageSize = pageSize[0];
        }
        return this;
    }


    /**
     * 根据表达式添加自定义sql条件
     *
     * @param condition 表达式，当为true时添加条件
     * @param sqlFormat SQL模板，参数值使用?代替，如：<code>username = ? and nickname like '%?%'</code>
     * @param args      参数
     * @return 返回Query对象
     */
    public Query last(boolean condition, String sqlFormat, Object... args) {
        if (condition) {
            last(sqlFormat, args);
        }
        return this;
    }

    // ------------ 基本条件 ------------./e


    /**
     * 使用inner join连接多表
     *
     * @param tableNames  表名
     * @param columnNames 列名
     */
    public Query fromInnerJoin(String[] tableNames, String[] columnNames, String[] selects) {
        if (tableNames == null
                || columnNames == null
                || tableNames.length == 0
                || tableNames.length != columnNames.length) {
            throw new IllegalArgumentException("parameter not match");
        }
        for (int i = tableNames.length - 1; i > 0; i--) {
            tables.innerJoin(tableNames[i], columnNames[i], Arrays.asList(selects), tableNames[i - 1], columnNames[i - 1]);
        }
        return this;
    }

    public Query fromInnerJoin(String table1, String column1, String table2, String column2) {
        tables.innerJoin(table2, column2, null, table1, column1);
        return this;
    }

    /**
     * 使用left join连接多表
     *
     * @param tableNames  表名
     * @param columnNames 列名
     */
    public Query fromLeftJoin(String[] tableNames, String[] columnNames, String[] selects) {
        if (tableNames == null
                || columnNames == null
                || tableNames.length == 0
                || tableNames.length != columnNames.length) {
            throw new IllegalArgumentException("parameter not match");
        }
        for (int i = tableNames.length - 1; i > 0; i--) {
            tables.leftJoin(tableNames[i], columnNames[i], Arrays.asList(selects), tableNames[i - 1], columnNames[i - 1]);
        }
        return this;
    }

    public Query fromLeftJoin(String table1, String column1, String table2, String column2) {
        tables.leftJoin(table2, column2, null, table1, column1);
        return this;
    }

    /**
     * 使用right join连接多表
     *
     * @param tableNames  表名
     * @param columnNames 列名
     */
    public Query fromRightJoin(String[] tableNames, String[] columnNames, String[] selects) {
        if (tableNames == null
                || columnNames == null
                || tableNames.length == 0
                || tableNames.length != columnNames.length) {
            throw new IllegalArgumentException("parameter not match");
        }
        for (int i = tableNames.length - 1; i > 0; i--) {
            tables.rightJoin(tableNames[i], columnNames[i], Arrays.asList(selects), tableNames[i - 1], columnNames[i - 1]);
        }
        return this;
    }

    public Query fromRightJoin(String table1, String column1, String table2, String column2) {
        tables.rightJoin(table2, column2, null, table1, column1);
        return this;
    }


    // ------------ getter & setter ------------./s
    public boolean isQueryAll() {
        this.queryAll = !expressions.isHasLimit();
        return queryAll;
    }

    public boolean getQueryAll() {
        this.queryAll = !expressions.isHasLimit();
        return queryAll;
    }

    protected boolean isTotalSet() {
        return totalSet;
    }

    protected long getTotal() {
        return total;
    }

    protected void setTotal(long total) {
        this.total = total;
        this.totalSet = true;
    }

    public long getOffset() {
        return offset;
    }

    public void setOffset(long offset) {
        this.offset = offset;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public Expressions getExpressions() {
        return expressions;
    }


    public boolean getForce() {
        return force;
    }

    public boolean isForce() {
        return force;
    }

}
